package com.dao.impl;


import com.dao.ClerkDao;
import com.entity.Clerk;
import com.result.QueryResult;
import com.util.DBUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lenovo
 */
public class ClerkDaoImpl implements ClerkDao {


    @Override
    public QueryResult selectByPage(Integer offset, Integer size) {
        try {
            QueryResult queryResult = new QueryResult();
            //1.查询页面数据
            String sql = "select `id`,`name`,`sex`,`telephone`,`position`,`salary`,`img` from clerk limit ?,?";
            PreparedStatement preparedStatement = DBUtil.createPreparedStatement(sql);
            //设置分页条件
            preparedStatement.setInt(1, offset);
            preparedStatement.setInt(2, size);
            ResultSet resultSet = DBUtil.execQuery(preparedStatement);
            List<Clerk> clerkList = new ArrayList<>();
            // 循环滚动游标
            while (resultSet.next()) {
                Clerk clerk = new Clerk();
                clerk.setId(resultSet.getInt("id"));
                clerk.setName(resultSet.getString("name"));
                clerk.setSex(resultSet.getString("sex"));
                clerk.setTelephone((resultSet.getString("telephone")));
                clerk.setPosition(resultSet.getString("position"));
                clerk.setSalary(resultSet.getInt("salary"));
                clerk.setImg(resultSet.getString("img"));
                // 把获取了字段特征的对象添加到集合中
                clerkList.add(clerk);
            }
            queryResult.setList(clerkList);
            //2.查询总记录数
            String sql1="select count(*) from clerk";
            PreparedStatement preparedStatement1 =DBUtil.createPreparedStatement(sql1);
            ResultSet resultSet1 = DBUtil.execQuery(preparedStatement1);
            if(resultSet1.next()){
                queryResult.setCount(resultSet1.getLong(1));
            }
            return queryResult;
        }catch (Exception e){
            System.out.println("查询失败!");
            return null;
        }
    }

    @Override
    public Integer insert(Clerk clerk) {
        try{
            String insert="insert into clerk values(null,?,?,?,?,?,?)";
            PreparedStatement preparedStatement= DBUtil.createPreparedStatement(insert);

            preparedStatement.setString(1,clerk.getName());
            preparedStatement.setString(2,clerk.getSex());
            preparedStatement.setString(3,clerk.getTelephone());
            preparedStatement.setString(4, clerk.getPosition());
            preparedStatement.setInt(5, clerk.getSalary());
            preparedStatement.setString(6,clerk.getImg());

            int i= DBUtil.execUpdate(preparedStatement);
            DBUtil.close();
            return i;
        }catch(Exception e){
            System.out.println("添加失败！");
        }

        return null;
    }

    @Override
    public Integer delete(Integer id) {
        try {
            String sql = "delete from clerk where id=?";
            PreparedStatement preparedStatement =DBUtil.createPreparedStatement(sql);
            preparedStatement.setInt(1,id);
            DBUtil.execUpdate(preparedStatement);
        }catch (Exception e){
            System.out.println("删除失败!");
        }
        return null;
    }

    @Override
    public void update(Clerk clerk) {
        try {
            String sql = "update clerk  set `name`=?,`sex`=?,`telephone`=?,`position`=?,`salary`=?,`img`=? where id=?";
            PreparedStatement preparedStatement =DBUtil.createPreparedStatement(sql);
            preparedStatement.setString(1,clerk.getName());
            preparedStatement.setString(2,clerk.getSex());
            preparedStatement.setString(3,clerk.getTelephone());
            preparedStatement.setString(4, clerk.getPosition());
            preparedStatement.setInt(5, clerk.getSalary());
            preparedStatement.setString(6,clerk.getImg());
            preparedStatement.setInt(7, clerk.getId());

            int i = DBUtil.execUpdate(preparedStatement);
        }catch (Exception e){
            System.out.println("修改失败!");
        }
    }

    @Override
    public QueryResult searchLikeName(String searchName,Integer offset, Integer size) {
        try {
            QueryResult queryResult = new QueryResult();
            String sql = "select `id`,`name`,`sex`,`telephone`,`position`,`salary`," +
                    "`img` from clerk where name like '%"+searchName+"%' limit ?,? ";
            PreparedStatement preparedStatement =DBUtil.createPreparedStatement(sql);
            //设置分页条件
            preparedStatement.setInt(1, offset);
            preparedStatement.setInt(2, size);
            ResultSet resultSet = DBUtil.execQuery(preparedStatement);
            List<Clerk> clerkList = new ArrayList<>();
            // 循环滚动游标
            while (resultSet.next()) {
                Clerk clerk = new Clerk();
                clerk.setId(resultSet.getInt("id"));
                clerk.setName(resultSet.getString("name"));
                clerk.setSex(resultSet.getString("sex"));
                clerk.setTelephone((resultSet.getString("telephone")));
                clerk.setPosition(resultSet.getString("position"));
                clerk.setSalary(resultSet.getInt("salary"));
                clerk.setImg(resultSet.getString("img"));
                // 把获取了字段特征的对象添加到集合中
                clerkList.add(clerk);
            }
            queryResult.setList(clerkList);
            //2.查询总记录数
            String sql1="select count(*) from clerk where name like '%"+searchName+"%'";
            PreparedStatement preparedStatement1 =DBUtil.createPreparedStatement(sql1);
            ResultSet resultSet1 = DBUtil.execQuery(preparedStatement1);
            if(resultSet1.next()){
                queryResult.setCount(resultSet1.getLong(1));
            }
            DBUtil.close();
            return queryResult;
        }catch (Exception e){
            System.out.println("查询失败!");
            return null;
        }
    }
}
